using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using LocalBoard.Domain;

using Microsoft.ApplicationBlocks.Data;

namespace LocalBoard.Persistence.SqlAccessor
{
	/// <summary>
	/// Summary description for SqlMessageAccessor.
	/// </summary>
	public class SqlMessageAccessor : SqlDataAccessBase, IMessageAccessor
	{
		public static readonly SqlMessageAccessor Instance = new SqlMessageAccessor();

		private SqlMessageAccessor()
		{}

		#region IMessageAccessor Members
		public ArrayList GetMessageListByBoardId(int boardId, int numberPerPage, int pageNo)
		{
			ArrayList messageList = new ArrayList();
			using(SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetMessageListByBoardId", boardId, numberPerPage, pageNo))
			{
				while(dataReader.Read())
				{
					messageList.Add(GetMessageFromResultRow(dataReader));
				}
			}
			return messageList;
		}

		public ArrayList GetMessageListByClubId(int clubId, int numberPerPage, int pageNo)
		{
			ArrayList messageList = new ArrayList();
			using(SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetMessageListByClubId", clubId, numberPerPage, pageNo))
			{
				while(dataReader.Read())
				{
					messageList.Add(GetMessageFromResultRow(dataReader));
				}
			}
			return messageList;
		}

		public Message Get(int id)
		{
			using(SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetMessageByMessageId", id))
			{
				if(dataReader.Read())
					return GetMessageFromResultRow(dataReader);
			}
			return null;
		}

		public ArrayList GetReplies(int id)
		{
			ArrayList replies = new ArrayList();
			using(SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetRepliesByMessageId", id))
			{
				while(dataReader.Read())
				{
					replies.Add(GetMessageFromResultRow(dataReader));
				}
			}
			return replies;
		}

		public int GetTotalRootMessageCountByBoardId(int boardId)
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "GetTotalRootMessageCountByBoardId", boardId));
		}

		public int GetTotalRootMessageCountByClubId(int clubId)
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "GetTotalRootMessageCountByClubId", clubId));
		}

		public int Insert(Message message)
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "InsertMessage", message.Subject, message.Body, message.PostedByUserName, message.PostedByUserId, Message.GetMessageTypeId(message), (message.Author == null) ? 0 : message.Author.Id, message.ReplyToId, message.BoardId, message.ClubId, message.DataXml.OuterXml));
		}

		public void Update(Message message)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "UpdateMessage", message.Id, message.Subject, message.Body, message.DataXml.OuterXml);
		}

		public void IncreaseHit(Message message)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "IncreaseMessageHit", message.Id);
		}

		public void Delete(int id)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteMessage", id);
		}

		/// <summary>
		/// Insert a vote for a message
		/// </summary>
		/// <param name="id">Message id</param>
		/// <param name="profileId">Profile Id</param>
		/// <param name="point">Give a point to a message</param>
		public void InsertVote(int id, int profileId, int point)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "InsertVoteForMessage", id, profileId, point);
		}

		/// <summary>
		/// Get list of <see cref="Vote">Vote</see>s that user's casted for a message
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public ArrayList GetVoteList(int id)
		{
			ArrayList votes = new ArrayList();
			SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetVoteListByMessageId", id);

			while(dataReader.Read())
			{
				votes.Add(new Vote(DataConverter.GetInteger(dataReader["Message_Id"]), DataConverter.GetInteger(dataReader["Profile_Id"]), DataConverter.GetInteger(dataReader["Point"])));
			}
			dataReader.Close();
			return votes;
		}
		#endregion

		private Message GetMessageFromResultRow(IDataReader dataReader)
		{
			int messageOId = DataConverter.GetInteger(dataReader["Id"]);
			SqlProfileAccessor userAccessor = SqlProfileAccessor.Instance;
			SqlCommentAccessor commentAccessor = SqlCommentAccessor.Instance;
			SqlAttachmentAccessor attachmentAccessor = SqlAttachmentAccessor.Instance;
			SqlPollItemAccessor pollItemAccessor = SqlPollItemAccessor.Instance;

			if (Message.GetMessageType(DataConverter.GetInteger(dataReader["MessageType"])) == MessageType.Message)
			{
				Message m = new Message(DataConverter.GetInteger(messageOId), DataConverter.GetInteger(dataReader["No"]), DataConverter.GetString(dataReader["Subject"]), DataConverter.GetString(dataReader["Body"]), DataConverter.GetString(dataReader["PostedByUserName"]), DataConverter.GetString(dataReader["PostedByUserId"]), DataConverter.GetInteger(dataReader["Hits"]), DataConverter.GetDateTime(dataReader["CreateDate"]), SqlProfileAccessor.Instance.Get(DataConverter.GetInteger(dataReader["Author_Id"])), DataConverter.GetInteger(dataReader["Board_Id"]), DataConverter.GetInteger(dataReader["Club_Id"]), DataConverter.GetInteger(dataReader["ReplyTo_Id"]), GetReplies(messageOId), commentAccessor.GetCommentListByMessageId(messageOId), attachmentAccessor.GetAttachmentListByMessageId(messageOId));
				m.X = DataConverter.GetInteger(dataReader["X"]);
				m.Y = DataConverter.GetInteger(dataReader["Y"]);
				m.Z = DataConverter.GetInteger(dataReader["Z"]);
				return m;
			}
			else
			{
				PollMessage m = new PollMessage(DataConverter.GetInteger(messageOId), DataConverter.GetInteger(dataReader["No"]), DataConverter.GetString(dataReader["Subject"]), DataConverter.GetString(dataReader["Body"]), DataConverter.GetString(dataReader["PostedByUserName"]), DataConverter.GetString(dataReader["PostedByUserId"]), DataConverter.GetInteger(dataReader["Hits"]), DataConverter.GetDateTime(dataReader["CreateDate"]), SqlProfileAccessor.Instance.Get(DataConverter.GetInteger(dataReader["Author_Id"])), DataConverter.GetInteger(dataReader["Board_Id"]), DataConverter.GetInteger(dataReader["Club_Id"]), DataConverter.GetInteger(dataReader["ReplyTo_Id"]), GetReplies(messageOId), commentAccessor.GetCommentListByMessageId(messageOId), attachmentAccessor.GetAttachmentListByMessageId(messageOId), pollItemAccessor.GetPollItemListByMessageId(messageOId), pollItemAccessor.GetVoterIdListByMessageId(messageOId));
				m.X = DataConverter.GetInteger(dataReader["X"]);
				m.Y = DataConverter.GetInteger(dataReader["Y"]);
				m.Z = DataConverter.GetInteger(dataReader["Z"]);
				return m;
			}
		}
	}
}